﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IWS.SiteManager.Core.Model;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Transactions;

namespace IWS.SiteManager.Core
{
	public class DBFactory
	{
		public const int VERSION = 1;

		public static Model.SiteManagerDataContext GetProvider()
		{
			var conn = GetDefaultConnectionString();
			if (!string.IsNullOrEmpty(conn))
				return new Model.SiteManagerDataContext(conn);

			return new Model.SiteManagerDataContext();
		}
		public static bool SqlServerDatabaseExists(string connection)
		{
			//check for our system table.. if it does not exist, it assumes this is a new empty datbase. 
			using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connection))
			{
				//look for the system table. 
				string sql = "SELECT count(*) as qty from sysobjects where xtype = 'U' and name = 'SiteManagerSystem'";
				System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
				conn.Open();
				int? count = cmd.ExecuteScalar() as int?;
				conn.Close(); //redundant

				return count.HasValue && count.Value > 0;
			}
		}

		public static void CheckDatabaseVersion(string connection)
		{
			if (!SqlServerDatabaseExists(connection))
				RunVersion1Install(connection);

			//TODO: this is a work in progres... I'm toying with ways to auto-update data schema. 

			//SiteManagerDataContext db = new SiteManagerDataContext(connection);

			//var sys = db.SiteManagerSystems.SingleOrDefault();

			//bool success = true;
			//if (sys == null) //new install
			//   success = RunVersion1(connection);


			////these should waterfall.  when version 2 is ready. case 2 will start immediately after. so version 1, 2, 3, etc will happen in sequence
			//switch (sys.Version)
			//{
			//   case 1:
			//      success = RunVersion1(connection);
			//      if (!success)
			//         return success;
			//   //placeholder for case 2
			//      return success;
			//}

			//return success;
		}

		public static void RunVersion1Install(string connection)
		{
			using (SqlConnection conn = new SqlConnection(connection))
			{
				conn.Open();
				using (SqlTransaction tran = conn.BeginTransaction())
				{
					//install schema				
					List<string> scripts = GetCommandsFromBatch(DBScripts.version1_schema);
					for (int ix = 0; ix < scripts.Count; ix++)
					{
						SqlCommand cmd = new SqlCommand(scripts[ix], conn, tran);
						cmd.CommandType = System.Data.CommandType.Text;
						cmd.ExecuteNonQuery();
					}


					//insert data
					scripts = GetCommandsFromBatch(DBScripts.version1_data);
					for (int ix = 0; ix < scripts.Count; ix++)
					{
						SqlCommand cmd = new SqlCommand(scripts[ix], conn, tran);
						cmd.CommandType = System.Data.CommandType.Text;
						cmd.ExecuteNonQuery();
					}

					tran.Commit();
				}
				conn.Close();
			}
		}

		private static List<string> GetCommandsFromBatch(string batchsql)
		{
			Regex cmdSearch = new Regex("\r\nGO\r\n", RegexOptions.IgnoreCase);
			int last_index = 0;
			List<string> results = new List<string>();
			MatchCollection matches = cmdSearch.Matches(batchsql);
			foreach (Match m in matches)
			{
				results.Add(batchsql.Substring(last_index, m.Index - last_index));
				last_index = m.Index + 6;
			}

			if (matches.Count <= 1)
				results.Add(batchsql);

			return results;
		}

		public static void InstallMembership(string connection)
		{
			using (SqlConnection conn = new SqlConnection(connection))
			{
				conn.Open();
				using (SqlTransaction tran = conn.BeginTransaction())
				{
					//install schema				
					List<string> scripts = GetCommandsFromBatch(DBScripts.version1_membership_schema);
					for (int ix = 0; ix < scripts.Count; ix++)
					{
						SqlCommand cmd = new SqlCommand(scripts[ix], conn, tran);
						cmd.CommandType = System.Data.CommandType.Text;
						cmd.ExecuteNonQuery();
					}

					tran.Commit();
				}
				conn.Close();
			}
		}

		public static string GetDefaultConnectionString()
		{
			var name = Properties.Settings.Default.DefaultConnectionName;
			var conn = System.Configuration.ConfigurationManager.ConnectionStrings[name];

			if (conn != null)
				return conn.ConnectionString;

			return null;
		}
	}
}
